package net.arogarth.aws.android.manager;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseAdapter {
	public final static String APP_DB_NAME = "net_arogarth_awsmanager";
	public final static String APP_DB_TABLE_ACCOUNT_NAME = "aws_accounts";
	public final static String APP_DB_TABLE_KERNEL_IDS = "kernels";
	public final static String APP_DB_TABLE_REGIONS = "regions";
	public final static String APP_DB_TABLE_AVAILABLE_ZONES = "available_zones";
	public final static String APP_DB_TABLE_INSTANCE_TYPES = "instance_types";
	public final static String APP_DB_TABLE_ROUTE53_ZONES = "route53_zones";
	
	public class DatabaseHelper extends SQLiteOpenHelper {
		public final static int DATABASE_VERSION = 1;


		public DatabaseHelper(Context context) {
			super(context, APP_DB_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + APP_DB_TABLE_ACCOUNT_NAME + "("
					+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ "name TEXT NOT NULL,"
					+ "default_region TEXT NOT NULL,"
					+ "accesskey TEXT NOT NULL,"
					+ "secretkey TEXT)");
			
			db.execSQL("CREATE TABLE " + APP_DB_TABLE_KERNEL_IDS + "("
					+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ "kernel_id TEXT NOT NULL)");
			
			String[] kernels = {"aki-03e8dc77", "aki-14340160", "aki-0d9baf79", "aki-119ca965", "aki-13556367",
					"aki-17765c63", "aki-1e98b26a", "aki-20b99354", "aki-22526256", "aki-2296be56", "aki-2a123a5e",
					"aki-2e4a7f5a", "aki-3190bb45", "aki-33face47", "aki-34795240", "aki-381a2b4c", "aki-3ec0e84a",
					"aki-40c0e834", "aki-41664c35", "aki-41eec435", "aki-46ae9832", "aki-4ac0e83e", "aki-4d81a939",
					"aki-4e40703a", "aki-4e794c3a", "aki-4ec0e83a", "aki-4f2c183b", "aki-4feec43b", "aki-51e2c925",
					"aki-550d2521", "aki-59ebc02d", "aki-5cbf9528", "aki-5f7f552b", "aki-5fe1ca2b", "aki-60695814",
					"aki-624c6716", "aki-62695816", "aki-673e1513", "aki-6a0d251e", "aki-6b301a1f", "aki-6baf9b1f", 
					"aki-6c063318", "aki-6d022919", "aki-718bbf05", "aki-74c9e100", "aki-780d250c", "aki-7b341e0f",
					"aki-831e2af7", "aki-83d7fff7", "aki-83e9dcf7", "aki-880028fc", "aki-89775dfd", "aki-8c4174f8",
					"aki-93487ce7", "aki-9490a0e0", "aki-95a68ee1", "aki-9a0028ee", "aki-9d604ae9", "aki-a22a01d6",
					"aki-a5e7d2d1", "aki-a7b893d3", "aki-a90a3ddd", "aki-aa6a41de", "aki-b30f25c7", "aki-b9e7d2cd",
					"aki-bdf2c6c9", "aki-c0d4feb4", "aki-c2614ab6", "aki-cb4d67bf", "aki-cf3913bb", "aki-d25972a6",
					"aki-d7eec5a3", "aki-d99bb0ad", "aki-d9ecc7ad", "aki-dbe4d1af", "aki-dc6151a8", "aki-de123aaa",
					"aki-e0cbe394", "aki-e1664c95", "aki-e2407396", "aki-e2cbe396", "aki-e38cb997", "aki-e6cbe392",
					"aki-e9cbe09d", "aki-ee52799a", "aki-eecae29a", "aki-f2634886", "aki-f861548c", "aki-fd092289",
					"aki-fe61548a", "aki-fe93a58a", "aki-ffffd48b"};
			
			for(String kernel : kernels)
				db.execSQL(String.format("INSERT INTO %s (kernel_id) VALUES ('%s')", APP_DB_TABLE_KERNEL_IDS, kernel));
			
			db.execSQL("CREATE TABLE " + APP_DB_TABLE_REGIONS + "(" +
					"id INTEGER PRIMARY KEY AUTOINCREMENT," +
					"account_id INTEGER NOT NULL," +
					"region TEXT NOT NULL" +
					")");
					
			db.execSQL("CREATE TABLE " + APP_DB_TABLE_AVAILABLE_ZONES + "("
					+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ "account_id INTEGER NOT NULL,"
					+ "region TEXT NOT NULL,"
					+ "zone TEXT NOT NULL,"
					+ "state TEXT NOT NULL)");
			
			String[] instanceTypes = {"t1.micro", "m1.small", "m1.large", "m1.xlarge", "c1.medium", "c1.xlarge",
					"m2.xlarge", "m2.2xlarge", "m2.4xlarge"};
			
			db.execSQL("CREATE TABLE " + APP_DB_TABLE_INSTANCE_TYPES + "(" +
					"id INTEGER PRIMARY KEY AUTOINCREMENT," +
					"instance_type TEXT NOT NULL" +
					")");
			
			for(String instanceType : instanceTypes)
				db.execSQL(String.format("INSERT INTO %s (instance_type) VALUES ('%s')", APP_DB_TABLE_INSTANCE_TYPES, instanceType));
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}

	}
	
	private Context context;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase database;
	
	public DatabaseAdapter() {
		this(AwsManager.myAppContext);
	}
	
	public DatabaseAdapter(Context context) {
		
		this.context = context;
	}
	
	public DatabaseAdapter open() {
		dbHelper = new DatabaseHelper(context);
		database = dbHelper.getWritableDatabase();
		
		return this;
	}
	
	public void close() {
		dbHelper.close();
	}
	
	public SQLiteDatabase getDatabase() {
		return this.database;
	}
	
	////////////////////////////////////////////////////////////////////////////////////////
	// AWS KernelIds
	////////////////////////////////////////////////////////////////////////////////////////
	public List<String> listKernelIds() {
		Cursor c = database.query(APP_DB_TABLE_KERNEL_IDS, null, null, null, null, null, null);
		
		List<String> kernels = new ArrayList<String>();

		while(c.moveToNext()) {
			String kernelId = c.getString(1);
			
			kernels.add(kernelId);
		}
		
		return kernels;
	}
}